//
//  Singleton.m
//  GSG
//
//  Created by dllo on 15/5/5.
//  Copyright (c) 2015年 dllo. All rights reserved.
//

#import "Singleton.h"

@implementation Singleton
+(instancetype)shareSingleton{
    static Singleton *singleton = nil;
    if (singleton == nil) {
        singleton = [[Singleton alloc] init];
        
    }
    return singleton;
}

// 打开数据库
- (void)openDB
{
    // 先获取沙盒路径
    NSArray *sandBox = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *sandBoxPath = [sandBox lastObject];
    NSLog(@"%@",sandBoxPath);
    
    // 拼接一个数据库的路径,如果这个路径下没有数据库则会创建一个数据库,并且打开这个数据库,如果有数据库了,则直接打开
    NSString *dbPath = [sandBoxPath stringByAppendingPathComponent:@"landing.sqlite"];
    // 开启数据库
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    if (result == SQLITE_OK) {
//        NSLog(@"数据库开启成功");
//        NSLog(@"%@",dbPath);
        
    }else{
//        NSLog(@"数据开启失败");
    }
    
}
// 创建一张数据库的表
- (void)createTable{
    // 第一件事先写sql语句
    NSString *sqlStr = @"create table if not exists land(number integer primary key autoincrement,songName text,singerName text,URL text)";
    
    // 执行sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
//        NSLog(@"创建表成功");
    }else{
//        NSLog(@"创建表失败");
    }
}
// 插入一个学生
- (void)insertLand:(Code *)land{
    NSString *insertLand = [NSString stringWithFormat:@"insert into land(songName, singerName,url) values ('%@', '%@', '%@')",land.songName,land.singerName,land.url];
    // 执行sql语句
    int result = sqlite3_exec(dbPoint, [insertLand UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
//        NSLog(@"插入学生成功");
    }else{
//        NSLog(@"插入学生失败");
    }
}
// 更新信息
- (void)updateLand:(Code *)land number:(NSInteger)num{
    
    // 主键不能修改
    NSString *updateSql = [NSString stringWithFormat:@"update land set songName = '%@', singerName = '%@', url = '%@' where number = %ld",land.songName,land.singerName,land.url,num];
    // 执行sql语句
    int result = sqlite3_exec(dbPoint, [updateSql UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
//        NSLog(@"修改成功");
    }else{
//        NSLog(@"修改失败");
    }
    
}
// 删除学生信息
- (void)deleteLand:(NSInteger)number{
    
    NSString *deleteLand = [NSString stringWithFormat:@"delete from land where number = %ld",number];
    int result = sqlite3_exec(dbPoint, [deleteLand UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
//        NSLog(@"删除成功");
    }else{
//        NSLog(@"删除失败");
    }
    
}

// 查询所有
- (NSMutableArray *)selectLand{
    
    // 查询的逻辑
    // 1.先从本地数据库中取出所有的数据
    // 2.然后将取出的每条数据赋给model
    // 3.最后存入数组,返回给调用的类
    // *是通配符,表示查询某个条件下数据库中所有字段的内容
    NSString *selectSQL = @"select * from land";
    // 需要创建一个跟随指针,用来循环遍历数据库中表的每行数据
    sqlite3_stmt *stmt = nil;
    
    // 第三个参数:查询语句字数限制,-1为不限制
    int result = sqlite3_prepare_v2(dbPoint, [selectSQL UTF8String], -1, &stmt, nil);
    // 这个方法相当于数据库,sql,跟随指针关联在一起,一同完成查询操作
    
    // 初始化学生的大数组
    NSMutableArray *landArr = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
//        NSLog(@"查询成功");
        // 开始遍历查询的数据库的每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 让跟随指针以行进行查询遍历,如果没有行,则停止循环
            // 满足循环条件,则逐列取出内容
            int number = sqlite3_column_int(stmt, 0);
            const unsigned char *songName = sqlite3_column_text(stmt, 1);
            const unsigned char *singerName = sqlite3_column_text(stmt, 2);
            const unsigned char *URL = sqlite3_column_text(stmt, 3);
            // 把取出来的内容进行类型转换
            NSInteger landNmuber = number;
            NSString *landsongName = [NSString stringWithUTF8String:(const char *)songName];
            NSString *landsingerName = [NSString stringWithUTF8String:(const char *)singerName];
            NSString *landURL = [NSString stringWithUTF8String:(const char *)URL];
            
            Code *land = [[Code alloc] init];
            
            land.songName = landsongName;
            land.singerName = landsingerName;
            land.url = landURL;
            land.number = landNmuber;
            [landArr addObject:land];
            [land release];
        }
    }else{
//        NSLog(@"查询失败");
    }
    return landArr;
}

- (void)dropTable{
    
    NSString *dropTable = [NSString stringWithFormat:@"drop table land"];
    int result = sqlite3_exec(dbPoint, [dropTable UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
//        NSLog(@"删除表成功");
    }else{
//        NSLog(@"删除表失败");
    }
}

- (void)closeDB{
    
    int result = sqlite3_close(dbPoint);
    if (result == SQLITE_OK) {
//        NSLog(@"关闭数据库成功");
    }else{
//        NSLog(@"关闭数据库失败");
    }
}

@end
